/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Models;

import Entity.Events;
import Entity.Feebacks;
import dbConnect.jdbcConnection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author Administrator
 */
public class adapterFeedBack {

    Connection con = null;
    adapterEvents adaptEvent = new adapterEvents();

    public adapterFeedBack() throws Exception {
        con = jdbcConnection.getConnection();
    }

    public ArrayList<Feebacks> getAllFeedBack() {
        try {
            ArrayList<Feebacks> listFeedBack = new ArrayList<Feebacks>();
            PreparedStatement pr = con.prepareStatement("select * from Feebacks order by codeFeeback desc");
            ResultSet rs = pr.executeQuery();
            while (rs.next()) {
                Feebacks feed = new Feebacks();
                feed.setCodeFeeback(rs.getInt(1));
                feed.setUserCustomer(rs.getString(2));
                feed.setContents(rs.getString(3));
                feed.setDate(rs.getString(4));
                ArrayList<Events> lstevent = adaptEvent.searchCodeEvent(rs.getInt(5));
                for (Events ev : lstevent) {
                    feed.setEvent(ev);
                }

                listFeedBack.add(feed);
            }
            return listFeedBack;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }
    //

    public ArrayList<Feebacks> getAllTopFeedBack() {
        try {
            ArrayList<Feebacks> listFeedBack = new ArrayList<Feebacks>();
            PreparedStatement pr = con.prepareStatement("select top 1 userCustomer,count(codeEvent)as Marks from Feebacks group by userCustomer order by Marks desc");
            ResultSet rs = pr.executeQuery();
            while (rs.next()) {
                Feebacks feed = new Feebacks();
                feed.setUserCustomer(rs.getString(1));
                feed.setMark(rs.getInt(2));
                listFeedBack.add(feed);
            }
            return listFeedBack;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public ArrayList<Feebacks> getAllReportFeebback() {
        try {
            ArrayList<Feebacks> listFeedBack = new ArrayList<Feebacks>();
            PreparedStatement pr = con.prepareStatement("select codeEvent,count(userCustomer)as mark from Feebacks group by codeEvent order by  mark desc");
            ResultSet rs = pr.executeQuery();
            while (rs.next()) {
                Feebacks feed = new Feebacks();
                ArrayList<Events> lstevent = adaptEvent.searchCodeEvent(rs.getInt(1));
                for (Events ev : lstevent) {
                    feed.setEvent(ev);
                }
                feed.setMark(rs.getInt(2));
                listFeedBack.add(feed);
            }
            return listFeedBack;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public ArrayList<Feebacks> searchDateFeedbackReport(String start, String end) {
        try {
            ArrayList<Feebacks> listFeedBack = new ArrayList<Feebacks>();
            PreparedStatement pr = con.prepareStatement("select codeEvent,count(userCustomer)as mark from Feebacks where date between ? and ? group by codeEvent,date order by mark desc");
            pr.setString(1, start);
            pr.setString(2, end);
            ResultSet rs = pr.executeQuery();
            while (rs.next()) {
                Feebacks feed = new Feebacks();

                ArrayList<Events> lstevent = adaptEvent.searchCodeEvent(rs.getInt(1));
                for (Events ev : lstevent) {
                    feed.setEvent(ev);
                }

                feed.setMark(rs.getInt(2));
                listFeedBack.add(feed);
            }
            return listFeedBack;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }
    //-------------------

    public ArrayList<Feebacks> searchDateFeedback(String start, String end) {
        try {
            ArrayList<Feebacks> listFeedBack = new ArrayList<Feebacks>();
            PreparedStatement pr = con.prepareStatement("select  userCustomer,count(codeEvent)as Marks from Feebacks where date between ? and ? group by userCustomer,date order by Marks desc");
            pr.setString(1, start);
            pr.setString(2, end);
            ResultSet rs = pr.executeQuery();
            while (rs.next()) {
                Feebacks feed = new Feebacks();
                feed.setUserCustomer(rs.getString(1));
                feed.setMark(rs.getInt(2));
                listFeedBack.add(feed);
            }
            return listFeedBack;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }

    //method search admin
    public ArrayList<Feebacks> searchFeedback(int code) {
        try {
            ArrayList<Feebacks> listFeedBack = new ArrayList<Feebacks>();
            PreparedStatement pr = con.prepareStatement("select * from Feebacks where codeFeeback=?");
            pr.setInt(1, code);
            ResultSet rs = pr.executeQuery();
            while (rs.next()) {
                Feebacks feed = new Feebacks();
                feed.setCodeFeeback(rs.getInt(1));
                feed.setUserCustomer(rs.getString(2));
                feed.setContents(rs.getString(3));
                feed.setDate(rs.getString(4));
                ArrayList<Events> lstevent = adaptEvent.searchCodeEvent(rs.getInt(5));
                for (Events ev : lstevent) {
                    feed.setEvent(ev);
                }
                listFeedBack.add(feed);
            }
            return listFeedBack;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }
//method insert admin

    public boolean insertFeedBack(String name, String content, String date, int codeEvent) {
        try {
            PreparedStatement pr = con.prepareStatement("insert into Feebacks values(?,?,?,?)");
            pr.setString(1, name);
            pr.setString(2, content);
            pr.setString(3, date);
            pr.setInt(4, codeEvent);
            int result = pr.executeUpdate();
            if (result > 0) {
                return true;
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }
    //method update admin

    public boolean updateFeedback(String name, String content, String date, int codeEvent, int code) {
        try {
            PreparedStatement pr = con.prepareStatement("update Feebacks set userCustomer=?,contents=?,date=?,codeEvent=? where codeFeeback=?");
            pr.setString(1, name);
            pr.setString(2, content);
            pr.setString(3, date);
            pr.setInt(4, codeEvent);
            pr.setInt(5, code);
            int result = pr.executeUpdate();
            if (result > 0) {
                return true;
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }
    //method delete admin

    public boolean deleteFeedBack(int code) {
        try {
            PreparedStatement pr = con.prepareStatement("delete Feebacks  where codeFeeback=?");
            pr.setInt(1, code);
            int result = pr.executeUpdate();
            if (result > 0) {
                return true;
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return false;
    }
}
